class: center, middle, inverse, title-slide .title[ # ISA 419: Data-Driven Security ] .subtitle[ ## 04: A Short Introduction to Pandas ] .author[ ### <br>Fadel M. Megahed, PhD <br><br> Endres Associate Professor <br> Farmer School of Business<br> Miami University<br><br> <a href="https://twitter.com/FadelMegahed"><svg viewBox="0 0 512 512" style="height:1em;position:relative;display:inline-block;top:.1em;fill:white;" xmlns="http://www.w3.org/2000/svg"> <path d="M459.37 151.716c.325 4.548.325 9.097.325 13.645 0 138.72-105.583 298.558-298.558 298.558-59.452 0-114.68-17.219-161.137-47.106 8.447.974 16.568 1.299 25.34 1.299 49.055 0 94.213-16.568 130.274-44.832-46.132-.975-84.792-31.188-98.112-72.772 6.498.974 12.995 1.624 19.818 1.624 9.421 0 18.843-1.3 27.614-3.573-48.081-9.747-84.143-51.98-84.143-102.985v-1.299c13.969 7.797 30.214 12.67 47.431 13.319-28.264-18.843-46.781-51.005-46.781-87.391 0-19.492 5.197-37.36 14.294-52.954 51.655 63.675 129.3 105.258 216.365 109.807-1.624-7.797-2.599-15.918-2.599-24.04 0-57.828 46.782-104.934 104.934-104.934 30.213 0 57.502 12.67 76.67 33.137 23.715-4.548 46.456-13.32 66.599-25.34-7.798 24.366-24.366 44.833-46.132 57.827 21.117-2.273 41.584-8.122 60.426-16.243-14.292 20.791-32.161 39.308-52.628 54.253z"></path></svg> <span class="citation">@FadelMegahed</span></a> <br> <a href="https://github.com/fmegahed/"><svg viewBox="0 0 496 512" style="height:1em;position:relative;display:inline-block;top:.1em;fill:white;" xmlns="http://www.w3.org/2000/svg"> <path d="M165.9 397.4c0 2-2.3 3.6-5.2 3.6-3.3.3-5.6-1.3-5.6-3.6 0-2 2.3-3.6 5.2-3.6 3-.3 5.6 1.3 5.6 3.6zm-31.1-4.5c-.7 2 1.3 4.3 4.3 4.9 2.6 1 5.6 0 6.2-2s-1.3-4.3-4.3-5.2c-2.6-.7-5.5.3-6.2 2.3zm44.2-1.7c-2.9.7-4.9 2.6-4.6 4.9.3 2 2.9 3.3 5.9 2.6 2.9-.7 4.9-2.6 4.6-4.6-.3-1.9-3-3.2-5.9-2.9zM244.8 8C106.1 8 0 113.3 0 252c0 110.9 69.8 205.8 169.5 239.2 12.8 2.3 17.3-5.6 17.3-12.1 0-6.2-.3-40.4-.3-61.4 0 0-70 15-84.7-29.8 0 0-11.4-29.1-27.8-36.6 0 0-22.9-15.7 1.6-15.4 0 0 24.9 2 38.6 25.8 21.9 38.6 58.6 27.5 72.9 20.9 2.3-16 8.8-27.1 16-33.7-55.9-6.2-112.3-14.3-112.3-110.5 0-27.5 7.6-41.3 23.6-58.9-2.6-6.5-11.1-33.3 2.6-67.9 20.9-6.5 69 27 69 27 20-5.6 41.5-8.5 62.8-8.5s42.8 2.9 62.8 8.5c0 0 48.1-33.6 69-27 13.7 34.7 5.2 61.4 2.6 67.9 16 17.7 25.8 31.5 25.8 58.9 0 96.5-58.9 104.2-114.8 110.5 9.2 7.9 17 22.9 17 46.4 0 33.7-.3 75.4-.3 83.6 0 6.5 4.6 14.4 17.3 12.1C428.2 457.8 496 362.9 496 252 496 113.3 383.5 8 244.8 8zM97.2 352.9c-1.3 1-1 3.3.7 5.2 1.6 1.6 3.9 2.3 5.2 1 1.3-1 1-3.3-.7-5.2-1.6-1.6-3.9-2.3-5.2-1zm-10.8-8.1c-.7 1.3.3 2.9 2.3 3.9 1.6 1 3.6.7 4.3-.7.7-1.3-.3-2.9-2.3-3.9-2-.6-3.6-.3-4.3.7zm32.4 35.6c-1.6 1.3-1 4.3 1.3 6.2 2.3 2.3 5.2 2.6 6.5 1 1.3-1.3.7-4.3-1.3-6.2-2.2-2.3-5.2-2.6-6.5-1zm-11.4-14.7c-1.6 1-1.6 3.6 0 5.9 1.6 2.3 4.3 3.3 5.6 2.3 1.6-1.3 1.6-3.9 0-6.2-1.4-2.3-4-3.3-5.6-2z"></path></svg> fmegahed</a> <br> <a href="mailto:fmegahed@miamioh.edu"><svg viewBox="0 0 512 512" style="height:1em;position:relative;display:inline-block;top:.1em;fill:white;" xmlns="http://www.w3.org/2000/svg"> <path d="M476 3.2L12.5 270.6c-18.1 10.4-15.8 35.6 2.2 43.2L121 358.4l287.3-253.2c5.5-4.9 13.3 2.6 8.6 8.3L176 407v80.5c0 23.6 28.5 32.9 42.5 15.8L282 426l124.6 52.2c14.2 6 30.4-2.9 33-18.2l72-432C515 7.8 493.3-6.8 476 3.2z"></path></svg> fmegahed@miamioh.edu</a><br> <a href="https://calendly.com/fmegahed"><svg viewBox="0 0 384 512" style="height:1em;position:relative;display:inline-block;top:.1em;fill:white;" xmlns="http://www.w3.org/2000/svg"> <path d="M202.021 0C122.202 0 70.503 32.703 29.914 91.026c-7.363 10.58-5.093 25.086 5.178 32.874l43.138 32.709c10.373 7.865 25.132 6.026 33.253-4.148 25.049-31.381 43.63-49.449 82.757-49.449 30.764 0 68.816 19.799 68.816 49.631 0 22.552-18.617 34.134-48.993 51.164-35.423 19.86-82.299 44.576-82.299 106.405V320c0 13.255 10.745 24 24 24h72.471c13.255 0 24-10.745 24-24v-5.773c0-42.86 125.268-44.645 125.268-160.627C377.504 66.256 286.902 0 202.021 0zM192 373.459c-38.196 0-69.271 31.075-69.271 69.271 0 38.195 31.075 69.27 69.271 69.27s69.271-31.075 69.271-69.271-31.075-69.27-69.271-69.27z"></path></svg> Automated Scheduler for Office Hours</a><br><br> ] .date[ ### Spring 2024 ] --- ## Quick Refresher of Last Class ✅ Understand the anatomy of a Python function, use arguments correctly, and construct your first function. ✅ Utilize built-in and anonymous functions (`map`, `lambda`, `filter`). ✅ Analyze your second dataset. --- ## Learning Objectives for Today's Class - Describe and create data frames - Import data using pandas - Utilize the `head()`, `tail()`, `shape()`, `info()`, `dtypes()`, `count()`, `value_count()`, `describe()`, `isnull().sum()` functions to quickly explore and summarize your dataset - Use the `loc[]` and `iloc[]` to select rows and columns - Use `assign()` to create new columns on your existing data frame. --- class: inverse, center, middle # Describing and Creating Data Frames --- ## What is Pandas? - Along with [polars](https://pola.rs/), it is one of the most popular data analysis and manipulation libraries in Python, designed to make working with 'relational' or 'labeled' data both easy and intuitive. - pandas supports the integration with many file formats or data sources out of the box (csv, excel, sql, json, parquet,…). Importing data from each of these data sources is provided by function with the prefix `read_*`. Similarly, the `to_*` methods are used to store data. [](https://pandas.pydata.org/) .footnote[ <html> <hr> </html> **Source:** The content and image are from the *Getting Started with Pandas* description in [pandas.pydata.org](https://pandas.pydata.org/docs/getting_started/index.html). The image can be directly accessed [here](https://pandas.pydata.org/docs/_images/02_io_readwrite.svg). ] --- ## What is a Data Frame? .pull-left[ - A data frame is a two-dimensional, size-mutable, potentially heterogeneous tabular data structure with labeled axes (rows and columns). - It is a primary data structure in pandas. - It is similar to a spreadsheet or SQL table, or a dictionary of Series objects. - It is generally the most commonly used pandas object. ] .pull-right[ <img src="data:image/png;base64,#https://pandas.pydata.org/docs/_images/01_table_dataframe.svg" alt="A data frame is a two-dimensional, size-mutable, potentially heterogeneous tabular data structure with labeled axes (rows and columns)." width="100%" style="display: block; margin: auto;" /> ] .footnote[ <html> <hr> </html> **Image Source:** The image is from the *Getting Started with Pandas* description in [pandas.pydata.org](https://pandas.pydata.org/docs/getting_started/index.html). The image can be directly accessed [here](https://pandas.pydata.org/docs/_images/01_table_dataframe.svg). ] --- ## What is a Series? .pull-left[ - Each column in a `DataFrame` is a `Series`. - When **selecting a single column of a pandas** `DataFrame`, the **result** is a pandas `Series`. To select the column, use the *column label* in between square brackets `[]`. - A Series is a one-dimensional data structure capable of holding data of any type (integer, string, float, python objects, etc.); it is **somewhat similar** to a list/dictionary. ] .pull-right[ [](https://pandas.pydata.org/) ] .footnote[ <html> <hr> </html> **Source:** The content and image are from the *Getting Started with Pandas* tutorial titled: [What kind of data does pandas handle?](https://pandas.pydata.org/docs/getting_started/intro_tutorials/01_table_oriented.html#min-tut-01-tableoriented) The image can be directly accessed [here](https://pandas.pydata.org/docs/_images/01_table_series.svg). ] --- ## Creating a Series We can easily create a `Series` from a list, or a dictionary. .pull-left[ **From a List** ```python import pandas as pd # Creating a Series from a list lst = [3, 5, 7, 9] s1 = pd.Series(lst) print(s1) ``` ``` ## 0 3 ## 1 5 ## 2 7 ## 3 9 ## dtype: int64 ``` ] .pull-right[ **From a Dictionary** ```python import pandas as pd # Creating a Series from a dictionary d = {'a': 3, 'b': 5, 'c': 7, 'd': 9} s2 = pd.Series(d) print(s2) ``` ``` ## a 3 ## b 5 ## c 7 ## d 9 ## dtype: int64 ``` ] **What are the differences between the two created series?** .can-edit.key-activity6[ Edit me to answer the question. ] --- ## Creating a Data Frame - There are several ways, where we can create a `DataFrame` in pandas. These include: .pull-left[ **From a Dictionary of Lists** ```python import pandas as pd # DataFrame from a dictionary of lists l1 = [1, 5] l2 = [2, 10] data = {'a': l1, 'b': l2, 'c': [20, 30]} df1 = pd.DataFrame(data) print(df1) ``` ``` ## a b c ## 0 1 2 20 ## 1 5 10 30 ``` ] .pull-right[ **From a List of Dictionaries** ```python import pandas as pd # DataFrame from a list of dictionaries dict1 = {'a': 1, 'b': 2} dict2 = {'a': 5, 'b': 10, 'c': 20} data = [dict1, dict2] df2 = pd.DataFrame(data) print(df2) ``` ``` ## a b c *## 0 1 2 NaN ## 1 5 10 20.0 ``` ] **Note the highlighted row in the second code chunk. What happened there?** .can-edit.key-activity7[ Edit me to answer the question. ] --- ## Class Activity: Creating a Data Frame <div class="countdown" id="timer_d54a3946" data-update-every="1" tabindex="0" style="top:0;right:0;font-size:2em;"> <div class="countdown-controls"><button class="countdown-bump-down">−</button><button class="countdown-bump-up">+</button></div> <code class="countdown-time"><span class="countdown-digits minutes">04</span><span class="countdown-digits colon">:</span><span class="countdown-digits seconds">00</span></code> </div> .panelset[ .panel[.panel-name[Task] - Use [Google Colab](https://colab.research.google.com/) to identify the correct piece of code that can be used to create two data frames: (a) a data frame from a single list, and (b) a data frame from two lists. ```python import pandas as pd my_lst = [1, 5, 2, 10] df3 = pd.DataFrame(my_lst) # A data frame from two lists l1 = [1, 5] l2 = [2, 10] # which of these produces the expected result, # where l1 and l2 are the columns of the data frame? df4a = pd.DataFrame(l1, l2) df4b = pd.DataFrame((l1, l2)) df4c = pd.DataFrame([l1, l2]) df4d = pd.DataFrame(zip(l1,l2)) ``` ] .panel[.panel-name[Comments] After running the code in [Google Colab](https://colab.research.google.com/), use the space below to write down **the correct data frame numbers** that can be used to create the two data frames: (a) a data frame from a single list, and (b) a data frame from two lists. <b style='color:black !important;'>Note the following:</b> - There are multiple ways to construct a data frame from two lists. - The results are <b style='color:black !important;'>not as pretty</b> as the ones we have seen before; but, this can be easily fixed. + <b style='color:black !important;'>Why?</b> **Write down your answer in the next panel.** + <b style='color:black !important;'>Take note of how in Fadel's walk through.</b> ] .panel[.panel-name[Your Answer] .can-edit.key-activity8[ - **Data Frame from a single list:** Edit me to answer the question. - **Data Frame from two lists:** Edit me to answer the question. - **Why are the results not as pretty as the ones we have seen before?** Edit me to answer the question. ] ] ] --- class: inverse, center, middle # Importing Data Using Pandas --- ## Importing Data Using Pandas [](https://pandas.pydata.org/) .footnote[ <html> <hr> </html> **Source:** The image is from the *Getting Started with Pandas* tutorial available at [pandas.pydata.org](https://pandas.pydata.org/docs/getting_started/index.html). The image can be directly accessed [here](https://pandas.pydata.org/docs/_images/02_io_readwrite.svg). ] --- ## Things to Consider while Importing Data with Pandas .font90[ - **Check the file extension:** Your <b style='color:black !important;'>choice of function</b> will depend on the file extension. - **Check the file path:** + **Local file:** * If the file is in the <b style='color:black !important;'>same directory</b> as your code, you can simply provide the <b style='color:black !important;'>file name</b>. * <b style='color:black !important;'>Otherwise</b>, you will need to provide the <b style='color:black !important;'>path</b> to the file. + **Remote file:** You will need to provide the <b style='color:black !important;'>URL to the file</b>. - **Inspect the file:** to know which <b style='color:black !important;'>default parameter values must be changed</b>. Things to consider: + Delimiter (in the case of .txt files) + Header (does it exist? if not, what should be used as column names?) + Index Column (if it exists, what is it? if not, what should be used as the index?) + Encoding (to be changed if file is not in English, or has names such as: François, José, Weiß, etc.) + Date Format (if the file has dates, what is the format?) + etc. ] --- ## Importing Data Using Pandas: CSVs <iframe src="https://pandas.pydata.org/docs/reference/api/pandas.read_csv.html" width="100%" height="490px" data-external="1"></iframe> .footnote[ <html> <hr> </html> **Source:** The content is from the `pandas.read_csv()` documentation available at [pandas.pydata.org](https://pandas.pydata.org/docs/reference/api/pandas.read_csv.html). ] --- ## Importing Data Using Pandas: CSVs [Example 1] Here is how you can import the **Simulated Equifax Breach Dataset** from a local file. ```python import pandas as pd equifax_df1 = pd.read_csv('../../data/simulated_equifax_breach_data.csv') equifax_df1.head() ``` ``` ## Name ... Driver License Number ## 0 Matthew Jenkins ... OA648128 ## 1 James Williams ... BB738847 ## 2 William Patton ... EL391232 ## 3 Eric Wells ... DZ520550 ## 4 William Walker ... GV282163 ## ## [5 rows x 6 columns] ``` --- ## Importing Data Using Pandas: CSVs [Example 2] Here is how you can import the **Simulated Equifax Breach Dataset** from a remote file. ```python import pandas as pd equifax_df2 = pd.read_csv('https://raw.githubusercontent.com/fmegahed/isa419/main/data/simulated_equifax_breach_data.csv') equifax_df2.head() ``` ``` ## Name ... Driver License Number ## 0 Matthew Jenkins ... OA648128 ## 1 James Williams ... BB738847 ## 2 William Patton ... EL391232 ## 3 Eric Wells ... DZ520550 ## 4 William Walker ... GV282163 ## ## [5 rows x 6 columns] ``` --- ## Importing Data Using Pandas: XML [Example 3] Here is how you can import the **open threat feeds** from the [ICS Sans API](https://isc.sans.edu/api/#threatfeeds). ```python open_threats = pd.read_xml('https://isc.sans.edu/api/threatfeeds/') open_threats.head() ``` ``` ## type ... frequency ## 0 adscore ... 0 ## 1 alphastrikelabs ... 0 ## 2 arbor ... 0 ## 3 bebloh ... 4800 ## 4 binaryedge ... 0 ## ## [5 rows x 6 columns] ``` --- ## Importing Data Using Pandas: JSON [Example 4] Here is how you can import the *Scapy Python Package Issues* from the [GitHub API](https://api.github.com/repos/secdev/scapy/issues). Note that *Scapy* is a powerful interactive packet manipulation package in Python (often used in *Pen Testing* and *Forensic Investigations*). ```python import pandas as pd # Reading JSON data scapy_issues = pd.read_json('https://api.github.com/repos/secdev/scapy/issues') scapy_issues.head() ``` ``` ## url ... pull_request ## 0 https://api.github.com/repos/secdev/scapy/issu... ... NaN ## 1 https://api.github.com/repos/secdev/scapy/issu... ... {'url': 'https://api.github.com/repos/secdev/s... ## 2 https://api.github.com/repos/secdev/scapy/issu... ... {'url': 'https://api.github.com/repos/secdev/s... ## 3 https://api.github.com/repos/secdev/scapy/issu... ... {'url': 'https://api.github.com/repos/secdev/s... ## 4 https://api.github.com/repos/secdev/scapy/issu... ... NaN ## ## [5 rows x 30 columns] ``` --- class: inverse, center, middle # Exploring and Summarizing Data --- ## Exploring and Summarizing Data .font90[ | Function | Description | Usage | |----------|------------------------|-------------| | `head()` | Returns the first `n` rows (default = 5) for the object based on position. | `equifax_df2.head()` | | `tail()` | Returns the last `n` rows (default = 5) for the object based on position. | `equifax_df2.tail()` | | `shape` | Returns the number of rows and columns in the DataFrame. | `equifax_df2.shape` | | `info()` | Prints information about the DataFrame, including the number of entries, the column names, and the data types. | `equifax_df2.info()`| | `dtypes` | Returns the data types of the columns. | `equifax_df2.dtypes` | ] --- ## Exploring and Summarizing Data (Cont.) .font90[ | Function | Description | Usage | |----------|--------------|-------| | `count()` | Returns the number of non-null values in each DataFrame column. | `equifax_df2.count()` | | `value_counts()` | Returns the frequency counts for each unique value in a column. <br> **Note: Applied per column.** | `equifax_df2['Name'].value_counts()` | | `nunique()` | Returns the number of unique values in a column. <br> **Note: Applied per column.** | `equifax_df2['Name'].nunique()` | | `describe()` | Provides descriptive statistics for numerical columns, but can also be used for object columns with `include='all'` parameter. | `equifax_df2.describe()`| | `isnull().sum()` | Returns the count of missing values (`NaN`) in each column. | `equifax_df2.isnull().sum()` | ] --- ## Explore and Summarize the Equifax Breach Dataset <div class="countdown" id="timer_723ec52b" data-update-every="1" tabindex="0" style="top:0;right:0;font-size:2em;"> <div class="countdown-controls"><button class="countdown-bump-down">−</button><button class="countdown-bump-up">+</button></div> <code class="countdown-time"><span class="countdown-digits minutes">05</span><span class="countdown-digits colon">:</span><span class="countdown-digits seconds">00</span></code> </div> .panelset[ .panel[.panel-name[Task] - Download the `simulated_equifax_breach_data.csv` file from [Canvas](https://miamioh.instructure.com/courses/209406/files/30864588?module_item_id=5050989). - Load the dataset into [Google Colab](https://colab.research.google.com/). - Using **pandas**, answer the questions in the next tabs. Note the first three are identical to what we did in the [previous class](https://fmegahed.github.io/isa419/spring2024/class03/03_python_functions.html?panelset1=task2&panelset2=task3&panelset3=task4#21) ] .panel[.panel-name[Task 1] - Display the first 5 rows of the dataset. ] .panel[.panel-name[Task 2] - Count the number of observations in our dataset. ] .panel[.panel-name[Task 3] - How many unique names are in the dataset? ] .panel[.panel-name[Task 4] - What are the type of the columns in the dataset? ] .panel[.panel-name[Task 5] - What are the descriptive statistics for the numeric columns in the dataset? ] ] --- class: inverse, center, middle # Subsetting Data in Pandas --- ## Subsetting Observations/Rows in Pandas [](https://pandas.pydata.org/Pandas_Cheat_Sheet.pdf) .footnote[ **Source:** The image is cropped from the [Pandas Cheat Sheet](https://pandas.pydata.org/Pandas_Cheat_Sheet.pdf). ] --- ## Common Issues with Subsetting - A common issue when **subsetting rows is that the index is not reset**, i.e., the index of the original data frame is retained. This <b style='color:black !important;'>can be problematic in the following subsequent scenarios:</b> + When you want to subset the data frame using the `iloc[]` or `loc[]` functions. + When you want to access the data frame using `.iat[]`. To <b style='color:black !important;'>overcome this issue</b>, use the **method `.reset_index(drop=True)`** to reset the index of the data frame. --- ## Common Issues with Subsetting (Cont.) - The `query()` method **cannot handle column names with spaces**. - To <b style='color:black !important;'>overcome this issue</b>, we can do the following: + first, use the **method `.rename()`** to rename the columns of the data frame; + then, use the **method `.query()`** to subset the data frame. **Example:** `df.rename(columns = {'old name': 'new_name'}, inplace = True)` **OR** + Use the method **`.query()`** to subset the data frame, but use **backticks** to enclose the column names with spaces. **Example:** ``df.query('`old name` > 5')`` **OR** + Use `clean_names()` from the [pyjanitor](https://pyjanitor-devs.github.io/pyjanitor/) package to clean the column names, and then use, `query()` to subset the data frame. --- class: inverse, center, middle # Creating New Columns in Pandas --- ## Creating New Columns in Pandas using `assign()` - The `assign()` method is used to create new columns on your existing data frame. - It returns a new data frame with the new columns added to the original data frame. - The method takes the form: `df.assign(new_column_name = new_column_values)`. - The `new_column_values` can be a single value, a list, or a series. - The method can also be used to create multiple columns at once. --- ## Demo: Creating New Columns using `assign()` In class, we will use `assign()` combined with `lambda` to create several new columns. <img src="data:image/png;base64,#../../figures/demo_pic.png" alt="A DALLE.E Created Figure of a Demo using the Prompt: Create a visually appealing and modern illustration that represents a code demonstration or programming workshop, suitable for a xaringan slide" width="70%" style="display: block; margin: auto;" /> .footnote[ <html> <hr> </html> **Image Source:** The image was generated using [DALL·E](https://openai.com/dall-e/) on 2024-02-07. **Bonus Tip:** We can use **`.reindex(columns = [some_list])`** to reorder/drop columns. ] --- class: inverse, center, middle # Recap --- ## Summary of Main Points By now, you should be able to do the following: - Describe and create data frames - Import data using pandas - Utilize the `head()`, `tail()`, `shape()`, `info()`, `dtypes()`, `count()`, `value_count()`, `describe()`, `isnull().sum()` functions to quickly explore and summarize your dataset - Use the `loc[]` and `iloc[]` to select rows and columns - Use `assign()` to create new columns on your existing data frame. --- ## 📝 Review and Clarification 📝 1. **Class Notes**: Take some time to revisit your class notes for key insights and concepts. 2. **Zoom Recording**: The recording of today's class will be made available on Canvas approximately 3-4 hours after the end of class. 3. **Questions**: Please don't hesitate to ask for clarification on any topics discussed in class. It's crucial not to let questions accumulate.